import database as DB
import json

def loadFromFile(fnm):
    with open(fnm) as f:
        return f.read()

data = json.loads(loadFromFile(input("Filename: ")))

DB.execute("DROP TABLE user;");
DB.execute("DROP TABLE student;");
DB.execute("DROP TABLE volunteer;");
DB.execute("DROP TABLE class_vol;");
DB.execute("DROP TABLE stu_vol;");
DB.execute("DROP TABLE user_notice;")

DB.execute('''
create table user (
	userId int AUTO_INCREMENT,
	userName char(64),
	class int,
	permission smallint,
	notices text,
	password char(255),
	primary key (userId)
)charset=utf8;
''')

DB.execute('''
create table student (
	stuId int,
	stuName char(64),
	volTimeInside int,
	volTimeOutside int,
	volTimeLarge int,
	primary key (stuId)
)charset=utf8;
''')

DB.execute('''
create table volunteer (
	volId int AUTO_INCREMENT,
	volName char(255),
	volDate char(64),
	volTime char(64),
	stuMax int,
	nowStuCount int,
	description text,
	status smallint,
	volTimeInside int,
	volTimeOutside int,
	volTimeLarge int,
	holderId int,
	primary key (volId)
)charset=utf8;
''')

DB.execute('''
create table stu_vol (
	volId int,
	stuId int,
	status smallint,
	volTimeInside int,
	volTimeOutside int,
	volTimeLarge int,
	thought text,
    picture text
)charset=utf8;
''')

DB.execute('''
create table class_vol (
	volId int,
	class int,
	stuMax int,
	nowStuCount int
)charset=utf8;
''')

DB.execute('''
create table user_notice (
	noticeTitle text,
	noticeText text,
	deadTime text,
	noticeId int AUTO_INCREMENT,
	primary key (noticeId)
)charset=utf8;
''')

for i in data["user"]:
    DB.execute("INSERT INTO user (userId, userName, class, permission, password) VALUES (%s, %s, %s, %s, %s)", tuple(i))

for i in data["student"]:
    DB.execute("INSERT INTO student (stuId, stuName, volTimeInside, volTimeOutside, volTimeLarge) VALUES (%s, %s, %s, %s, %s)", tuple(i))

for i in data["volunteer"]:
    DB.execute("INSERT INTO volunteer (volId, volName, volDate, volTime, stuMax, nowStuCount, description, status, volTimeInside, voltimeOutside, volTimeLarge, holderId) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", tuple(i))

for i in data["stu_vol"]:
    DB.execute("INSERT INTO stu_vol (volId, stuId, status, volTimeInside, volTimeOutside, volTimeLarge, thought) VALUES (%s, %s, %s, %s, %s, %s, %s)", tuple(i))

for i in data["class_vol"]:
    DB.execute("INSERT INTO class_vol (volId, class, stuMax, nowStuCount) VALUES (%s, %s, %s, %s)", tuple(i))

